<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h1 data-lake-id="ZIB7d" id="ZIB7d"><span data-lake-id="u277fbc36" id="u277fbc36">典型回答</span></h1>
  <p data-lake-id="u0bec0db1" id="u0bec0db1"><br></p>
  <p data-lake-id="ua6d47ba6" id="ua6d47ba6"><span data-lake-id="u00f4af18" id="u00f4af18">以下是一个比较典型的查询语句，其中包含了很多子句，其中有SELECT、FROM、JOIN、WHERE、GROUP BY、HAVING、ORDER BY和LIMIT。</span></p>
  <pre lang="java"><code>
SELECT name, COUNT(*)
FROM employees
JOIN departments ON employees.department_id = departments.id
WHERE employees.salary &gt; 50000
GROUP BY departments.name
HAVING COUNT(*) &gt; 10
ORDER BY name
LIMIT 5;
</code></pre>
  <p data-lake-id="ud71eb808" id="ud71eb808"><br></p>
  <p data-lake-id="u52dd955d" id="u52dd955d"><span data-lake-id="ud7315c7a" id="ud7315c7a">下面是InnoDB处理SQL查询的大致执行顺序，为啥说大致呢，因为这个顺序是逻辑上的执行顺序，实际的物理执行可能会有所不同。数据库优化器可能会根据统计信息、索引、查询类型等因素以不同的方式执行查询。</span></p>
  <p data-lake-id="u0b399692" id="u0b399692"><span data-lake-id="u65ca31a5" id="u65ca31a5">​</span><br></p>
  <ol list="ubc1f6825">
   <li fid="uece278fb" data-lake-id="uc36f0d71" id="uc36f0d71" data-lake-index-type="true"><strong><span data-lake-id="u437f1c4e" id="u437f1c4e">FROM</span></strong><span data-lake-id="uefdf3766" id="uefdf3766">：</span></li>
  </ol>
  <ul list="u2d512087" data-lake-indent="1">
   <li fid="u4d10e4a5" data-lake-id="u7b2f2100" id="u7b2f2100" data-lake-index-type="true"><span data-lake-id="ub3555ff7" id="ub3555ff7">第一步肯定要先识别出查询中涉及的所有表。</span></li>
  </ul>
  <ol list="ubc1f6825" start="2">
   <li fid="uece278fb" data-lake-id="ue9e0c543" id="ue9e0c543" data-lake-index-type="true"><strong><span data-lake-id="uf019c89a" id="uf019c89a">JOIN</span></strong><span data-lake-id="ufd358f6f" id="ufd358f6f">：</span></li>
  </ol>
  <ul list="ucdc2292c" data-lake-indent="1">
   <li fid="ua8302202" data-lake-id="u27a1cd1c" id="u27a1cd1c" data-lake-index-type="true"><span data-lake-id="u7f254e74" id="u7f254e74">根据FROM子句中的表，执行JOIN操作。如果有多个JOIN，数据库会根据内部算法和统计信息确定JOIN的顺序，以尝试优化查询效率。</span></li>
  </ul>
  <ol list="ubc1f6825" start="3">
   <li fid="uece278fb" data-lake-id="ue06bd48e" id="ue06bd48e" data-lake-index-type="true"><strong><span data-lake-id="u521d332b" id="u521d332b">WHERE</span></strong><span data-lake-id="uf77e35fc" id="uf77e35fc">：</span></li>
  </ol>
  <ul list="uca392336" data-lake-indent="1">
   <li fid="u236ca68a" data-lake-id="u9727b52b" id="u9727b52b" data-lake-index-type="true"><span data-lake-id="u4cf4f407" id="u4cf4f407">对JOIN操作的结果应用WHERE中的过滤条件。这一步会排除不符合条件的行。</span></li>
  </ul>
  <ol list="ubc1f6825" start="4">
   <li fid="uece278fb" data-lake-id="u0025651d" id="u0025651d" data-lake-index-type="true"><strong><span data-lake-id="u6fcd18aa" id="u6fcd18aa">GROUP BY</span></strong><span data-lake-id="u4af2f78a" id="u4af2f78a">：</span></li>
  </ol>
  <ul list="ud3fd3064" data-lake-indent="1">
   <li fid="u3bd8c929" data-lake-id="ue02eeb61" id="ue02eeb61" data-lake-index-type="true"><span data-lake-id="u31e025c8" id="u31e025c8">如果查询包含GROUP BY子句，此时对数据进行分组。分组操作通常在WHERE条件过滤之后进行。</span></li>
  </ul>
  <ol list="ubc1f6825" start="5">
   <li fid="uece278fb" data-lake-id="u1181c408" id="u1181c408" data-lake-index-type="true"><strong><span data-lake-id="ue6f50699" id="ue6f50699">HAVING</span></strong><span data-lake-id="u8936e901" id="u8936e901">：</span></li>
  </ol>
  <ul list="uf5a16209" data-lake-indent="1">
   <li fid="u70103167" data-lake-id="ude29a5ff" id="ude29a5ff" data-lake-index-type="true"><span data-lake-id="u2d92436b" id="u2d92436b">如果有HAVING子句，它会在GROUP BY之后应用。HAVING子句用于筛选分组后的数据集。</span></li>
  </ul>
  <ol list="ubc1f6825" start="6">
   <li fid="uece278fb" data-lake-id="u96abbc2e" id="u96abbc2e" data-lake-index-type="true"><strong><span data-lake-id="u39302411" id="u39302411">SELECT</span></strong><span data-lake-id="ueb9f279a" id="ueb9f279a">：</span></li>
  </ol>
  <ul list="ued3f8b13" data-lake-indent="1">
   <li fid="u8e789423" data-lake-id="u4c4b676c" id="u4c4b676c" data-lake-index-type="true"><span data-lake-id="ub0c48e6b" id="ub0c48e6b">选择特定的列。实际上，所需列的数据可能在执行过程的早期就已经被获取，但在此阶段会根据SELECT子句确定最终输出的列。</span></li>
  </ul>
  <ol list="ubc1f6825" start="7">
   <li fid="uece278fb" data-lake-id="ub41cdca8" id="ub41cdca8" data-lake-index-type="true"><strong><span data-lake-id="u2b1b8825" id="u2b1b8825">DISTINCT</span></strong><span data-lake-id="u54264ff7" id="u54264ff7">：</span></li>
  </ol>
  <ul list="u60d2a3e7" data-lake-indent="1">
   <li fid="u6a420d5e" data-lake-id="u5e0e68c6" id="u5e0e68c6" data-lake-index-type="true"><span data-lake-id="ub0e55488" id="ub0e55488">如果指定了DISTINCT关键字，此时会去除重复的行。</span></li>
  </ul>
  <ol list="ubc1f6825" start="8">
   <li fid="uece278fb" data-lake-id="u675ab7f9" id="u675ab7f9" data-lake-index-type="true"><strong><span data-lake-id="u05fad2d1" id="u05fad2d1">ORDER BY</span></strong><span data-lake-id="uaaaabc1f" id="uaaaabc1f">：</span></li>
  </ol>
  <ul list="ub4c5309d" data-lake-indent="1">
   <li fid="u7a764a93" data-lake-id="u0d9b3ac9" id="u0d9b3ac9" data-lake-index-type="true"><span data-lake-id="uc29afae2" id="uc29afae2">如果查询指定了ORDER BY，现在将对结果进行排序。注意，如果使用了GROUP BY，MySQL可能会利用这个排序作为GROUP BY的一部分。</span></li>
  </ul>
  <ol list="ubc1f6825" start="9">
   <li fid="uece278fb" data-lake-id="u03ecccae" id="u03ecccae" data-lake-index-type="true"><strong><span data-lake-id="u14967182" id="u14967182">LIMIT</span></strong><span data-lake-id="ue1b990f2" id="ue1b990f2">：</span></li>
  </ol>
  <ul list="u96c9f391" data-lake-indent="1">
   <li fid="u8baf8fdf" data-lake-id="uc7ab6cca" id="uc7ab6cca" data-lake-index-type="true"><span data-lake-id="u840214ac" id="u840214ac">最后，应用LIMIT子句来限制返回的行数。这通常是整个查询过程的最后一步。</span></li>
  </ul>
  <p data-lake-id="u88f7dc42" id="u88f7dc42"><span data-lake-id="u7ccc9c7b" id="u7ccc9c7b">​</span><br></p>
  <p data-lake-id="uad907ac1" id="uad907ac1"><span data-lake-id="u43f1caaf" id="u43f1caaf">如上面的SQL，我们是找出平均薪水超过50,000的部门中员工人数超过10人的前5个部门，他的执行过程如下：</span></p>
  <p data-lake-id="u05d1b747" id="u05d1b747"><span data-lake-id="ub308e811" id="ub308e811">​</span><br></p>
  <ol list="u42c9916c">
   <li fid="u70ce7b03" data-lake-id="u42430531" id="u42430531" data-lake-index-type="true"><strong><span data-lake-id="u1f350268" id="u1f350268">FROM</span></strong><span data-lake-id="uaa9f0b5a" id="uaa9f0b5a">：</span></li>
  </ol>
  <ul list="u22c06523" data-lake-indent="1">
   <li fid="u6cc58e83" data-lake-id="u28033b32" id="u28033b32" data-lake-index-type="true"><span data-lake-id="u67ca8fa8" id="u67ca8fa8">确定涉及的表：</span><span data-lake-id="u10d3a932" id="u10d3a932">employees</span><span data-lake-id="u04c8edcf" id="u04c8edcf">和</span><span data-lake-id="u360069c8" id="u360069c8">departments</span><span data-lake-id="uf44f2690" id="uf44f2690">。</span></li>
  </ul>
  <ol list="u42c9916c" start="2">
   <li fid="u70ce7b03" data-lake-id="ud59ee959" id="ud59ee959" data-lake-index-type="true"><strong><span data-lake-id="u058e7b18" id="u058e7b18">JOIN</span></strong><span data-lake-id="u14697750" id="u14697750">：</span></li>
  </ol>
  <ul list="u380f62ad" data-lake-indent="1">
   <li fid="ud47aea30" data-lake-id="ub4422ca4" id="ub4422ca4" data-lake-index-type="true"><span data-lake-id="ubef05c1e" id="ubef05c1e">根据</span><span data-lake-id="u48de67da" id="u48de67da">employees.department_id = departments.id</span><span data-lake-id="u33fd145e" id="u33fd145e">条件执行JOIN操作，将两个表的数据合并。</span></li>
  </ul>
  <ol list="u42c9916c" start="3">
   <li fid="u70ce7b03" data-lake-id="u90316963" id="u90316963" data-lake-index-type="true"><strong><span data-lake-id="u9a725f20" id="u9a725f20">WHERE</span></strong><span data-lake-id="u521b4bb8" id="u521b4bb8">：</span></li>
  </ol>
  <ul list="u19a46159" data-lake-indent="1">
   <li fid="ube492b54" data-lake-id="ucf7ac52b" id="ucf7ac52b" data-lake-index-type="true"><span data-lake-id="u955eda1a" id="u955eda1a">应用WHERE过滤条件</span><span data-lake-id="u0cad206a" id="u0cad206a">employees.salary &gt; 50000</span><span data-lake-id="u5f362bc8" id="u5f362bc8">，只保留薪水超过50,000的员工记录。</span></li>
  </ul>
  <ol list="u42c9916c" start="4">
   <li fid="u70ce7b03" data-lake-id="uf2053546" id="uf2053546" data-lake-index-type="true"><strong><span data-lake-id="u8de93c15" id="u8de93c15">GROUP BY</span></strong><span data-lake-id="u098c36ff" id="u098c36ff">：</span></li>
  </ol>
  <ul list="ue055fbc2" data-lake-indent="1">
   <li fid="u84d28507" data-lake-id="uc92ec082" id="uc92ec082" data-lake-index-type="true"><span data-lake-id="u2e76ce6f" id="u2e76ce6f">根据</span><span data-lake-id="u6aabc5fe" id="u6aabc5fe">departments.name</span><span data-lake-id="ud3830223" id="ud3830223">对结果集进行分组。</span></li>
  </ul>
  <ol list="u42c9916c" start="5">
   <li fid="u70ce7b03" data-lake-id="uaf8e0e24" id="uaf8e0e24" data-lake-index-type="true"><strong><span data-lake-id="u3b9548f5" id="u3b9548f5">HAVING</span></strong><span data-lake-id="u7dfcd0c1" id="u7dfcd0c1">：</span></li>
  </ol>
  <ul list="ub247b5d6" data-lake-indent="1">
   <li fid="ub54734ba" data-lake-id="uc484935a" id="uc484935a" data-lake-index-type="true"><span data-lake-id="u2ce3b6b0" id="u2ce3b6b0">应用HAVING条件</span><span data-lake-id="u8263edbd" id="u8263edbd">COUNT(*) &gt; 10</span><span data-lake-id="u2810a27d" id="u2810a27d">，筛选出员工人数超过10人的部门。</span></li>
  </ul>
  <ol list="u42c9916c" start="6">
   <li fid="u70ce7b03" data-lake-id="ud24da20e" id="ud24da20e" data-lake-index-type="true"><strong><span data-lake-id="u3aba7e11" id="u3aba7e11">SELECT</span></strong><span data-lake-id="uc98be931" id="uc98be931">：</span></li>
  </ol>
  <ul list="ub8382dfd" data-lake-indent="1">
   <li fid="uef13497f" data-lake-id="u24724fb7" id="u24724fb7" data-lake-index-type="true"><span data-lake-id="u3149c3c1" id="u3149c3c1">选择要显示的列：</span><span data-lake-id="ub0dba391" id="ub0dba391">name</span><span data-lake-id="u57443c8f" id="u57443c8f">（部门名称）和</span><span data-lake-id="u59e360bf" id="u59e360bf">COUNT(*)</span><span data-lake-id="u5f124e8d" id="u5f124e8d">（员工数量）。</span></li>
  </ul>
  <ol list="u42c9916c" start="7">
   <li fid="u70ce7b03" data-lake-id="ucb2905d5" id="ucb2905d5" data-lake-index-type="true"><strong><span data-lake-id="u2ba81625" id="u2ba81625">DISTINCT</span></strong><span data-lake-id="u0fc5fe46" id="u0fc5fe46">：</span></li>
  </ol>
  <ul list="u4717dced" data-lake-indent="1">
   <li fid="u017bd3b8" data-lake-id="u36212476" id="u36212476" data-lake-index-type="true"><span data-lake-id="uad460c2f" id="uad460c2f">此查询没有使用DISTINCT关键字，所以跳过。</span></li>
  </ul>
  <ol list="u42c9916c" start="8">
   <li fid="u70ce7b03" data-lake-id="u0b3d46d2" id="u0b3d46d2" data-lake-index-type="true"><strong><span data-lake-id="u06519763" id="u06519763">ORDER BY</span></strong><span data-lake-id="uf690f734" id="uf690f734">：</span></li>
  </ol>
  <ul list="u72e15301" data-lake-indent="1">
   <li fid="u4e96e80b" data-lake-id="u4ae65d62" id="u4ae65d62" data-lake-index-type="true"><span data-lake-id="u67cb41a6" id="u67cb41a6">根据</span><span data-lake-id="uc0ae6a7e" id="uc0ae6a7e">name</span><span data-lake-id="u6b423542" id="u6b423542">（部门名称）对结果集进行排序。</span></li>
  </ul>
  <ol list="u42c9916c" start="9">
   <li fid="u70ce7b03" data-lake-id="u49120c35" id="u49120c35" data-lake-index-type="true"><strong><span data-lake-id="u4f3e8b6b" id="u4f3e8b6b">LIMIT</span></strong><span data-lake-id="uce52b6d9" id="uce52b6d9">：</span></li>
  </ol>
  <ul list="uc08033ad" data-lake-indent="1">
   <li fid="u8e76b9af" data-lake-id="u442518b2" id="u442518b2" data-lake-index-type="true"><span data-lake-id="u8a5cbec6" id="u8a5cbec6">应用LIMIT，只返回前5条记录。</span></li>
  </ul>
  <p data-lake-id="u69eb83ce" id="u69eb83ce"><span data-lake-id="u88808690" id="u88808690">​</span><br></p>
  <p data-lake-id="u41aa9df6" id="u41aa9df6"><span data-lake-id="u29326848" id="u29326848">​</span><br></p>
 </body>
</html>